--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';




/*SELECT &outils.stragg(source) source,adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
FROM (
  SELECT 'uv' source ,g.* FROM se_gen_adresse g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gen_adresse g WHERE EXISTS (SELECT * FROM se_ach_fouradr a WHERE a.adresse_id=g.adresse_id)
  )
GROUP BY adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
HAVING Count(1)!=2
ORDER BY adresse_id*/

/*select * from se_gen_adresse g where not exists (select 1 from &erp.par_departement d where d.pays_cod=g.pays_cod and d.departement_cod=g.departement_cod)*/

DELETE FROM  se_gen_adresse ;

INSERT INTO se_gen_adresse
 (SELECT (SELECT adresse_id FROM se_ach_fouradr s WHERE s.fournisseur_cod=fuv.ID AND s.fouradr_cod='000') adresse_id,
    NULL strucjur_cod,
    langue traduction_cod,
    NULL expedzone_cod,
    0 enreg_id,
    (SELECT pays_cod FROM &erp.par_pays d
      WHERE d.pays_cod=Decode(fuv.pays,'FRA','FR',fuv.pays)) pays_cod,
    (SELECT departement_cod FROM &erp.par_departement d
      WHERE d.pays_cod=Decode(fuv.pays,'FRA','FR',fuv.pays)
        AND d.departement_cod=decode(fuv.pays,'FRA',SubStr(fuv.cp,0,2),NULL)) departement_cod,
    NULL region_cod,
    NULL prefnom_cod,
    Trim(nom) nom,
    NULL nom2,
    Trim(adr1) adr1,
    Trim(adr2) adr2,
    NULL adr3,
    NULL bp,
    Trim(cp) cpostal_cod,
    Trim(ville) ville_cod,
    Trim(telephone) tel1,
    Trim(tel2_moli) tel2,
    Trim(telecopie) fax1,
    NULL fax2,
    null telmobile1,
    NULL telmobile2,
    Trim(email) email,
    NULL siteweb,
    NULL contact_nom,
    Trim(tvacee) tvacee_no,
    SubStr(siret,0,14) siret_no,
    Trim(ape) ape_no,
    NULL gln_no,
    NULL cnuf_no,
    NULL rc_no,
    NULL villerc_lib,
    NULL capital,
    NULL logoachat_lib,
    NULL creatsoc_dat,
    NULL envoi_typ,
    NULL villeinsee_cod,
    NULL ediadr_no
    FROM uv_pfournisseurs fuv) ;

 UPDATE se_gen_adresse tf
 SET enreg_id = (select enreg_id FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id )
 WHERE EXISTS (SELECT 1 FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id);

 UPDATE se_gen_adresse SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

commit;
